﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using MSL.DataBase.Lib;

namespace MSL.DataBase.SQL
{
    /// <summary>
    /// 分页存储过程语句类
    /// </summary>
    public class SQLString
    {
        #region 变量

        private static StringBuilder sqlText = new StringBuilder();
        private const string OlepageAll = "SELECT {0} {1} FROM {2} WHERE {3}";
        private const string OlepageTopNotIn = "SELECT TOP {0} {4} FROM {1} WHERE {5} NOT IN(SELECT TOP {2} {5} FROM {1} WHERE {3} ORDER BY {6}) AND {3} ORDER BY {6}";
        private const string OlePageTop = @"SELECT TOP {0} {1} FROM {2} WHERE {3} IN(
                                                         SELECT TOP {0} {3} FROM (SELECT TOP {7} {3} FROM {2} WHERE {4} ORDER BY id DESC) AS SS ORDER BY {6}
                                                ) ORDER BY {5}";
        private const string SQLitepageAll = "SELECT {0} FROM {1} WHERE {2} {3} LIMIT {4} OFFSET {5}";
        private const string MySqlpageAll = "SELECT {0} FROM {1} WHERE {2} {3} LIMIT {4},{5}";
        #endregion

        #region 分页语句
        /// <summary>
        /// 不依赖于存储过程的分页(SqlServer)
        /// </summary>
        /// <param name="isTotal">如果isTotal为True，返回总数统计Sql；否则返回分页语句Sql</param>
        /// <returns></returns>
        private string SqlServerPage(int pageIndex, int pageSize, string whereStr, string tableName, string showFields, string orderBy, int recordCount, bool isTotal)
        {
            string sql = "";
            if (String.IsNullOrEmpty(whereStr))
            {
                whereStr = " (1=1) ";
            }

            if (isTotal)//执行总数统计
            {
                sql = String.Format("SELECT COUNT(*) AS Total FROM {0} WHERE {1} ", tableName, whereStr);
            }
            else
            {
                string strTemp = String.Empty;
                string strOrder = String.Empty;
                if (orderBy.ToLower().Contains("desc"))
                {
                    strTemp = "<(SELECT MIN";
                    strOrder = String.Format(" order by [{0}] ", orderBy);
                }
                else
                {
                    strTemp = ">(SELECT MAX";
                    strOrder = String.Format(" order by [{0}] ", orderBy);
                }

                sql = String.Format("SELECT TOP {0} {1} FROM {2} ", pageSize, showFields, tableName);

                //如果是第一页就执行以上代码，这样会加快执行速度
                if (pageIndex == 1)
                {
                    sql += String.Format(" WHERE {0} ", whereStr);
                    sql += strOrder;
                }
                else
                {
                    sql += String.Format(" WHERE [{0}] {1} ([{0}]) FROM (SELECT TOP {2} [{0}] FROM {3} WHERE {5} {4} ) AS tblTmp) AND {5} {4}", orderBy, strTemp, (pageIndex - 1) * pageSize, tableName, strOrder, whereStr);
                }
            }

            return sql;
        }
        /// <summary>
        /// 不依赖于存储过程的分页(Oracle)
        /// </summary>
        /// <returns></returns>
        private string OraclePage(int pageIndex, int pageSize, string whereStr, string tableName, string showFields, string orderBy, int recordCount, bool isTotal)
        {
            string sql = "";
            if (String.IsNullOrEmpty(whereStr))
            {
                whereStr = " (1=1) ";
            }

            if (isTotal)//执行总数统计
            {
                sql = String.Format("SELECT COUNT(*) AS Total FROM {0} WHERE {1} ", tableName, whereStr);
            }
            else
            {
                string strOrder = String.Format(" order by {0}", orderBy);

                int minRow = pageSize * (pageIndex - 1);
                int maxRow = pageSize * pageIndex;
                string selectSql = String.Format("SELECT {0} FROM {1} WHERE {2} {3}", showFields, tableName, whereStr, strOrder);
                sql = String.Format(@"SELECT b.* FROM (SELECT a.*, rownum AS rowIndex FROM({2}) a) b where b.rowIndex > {0} and b.rowIndex <= {1}", minRow, maxRow, selectSql);
            }
            return sql;
        }
        /// <summary>
        /// Access分页查询 ：主键和排序必须有，如果没有主键用唯一键也可
        /// </summary>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="whereStr">查询条件</param>
        /// <param name="tableName">表</param>
        /// <param name="primaryKey">主键</param>
        /// <param name="showFields">显示列</param>
        /// <param name="orderBy">排序：不带ORDER BY</param>
        /// <param name="recordCount">行数</param>
        /// <param name="isTotal">如果isTotal为True，返回总数统计Sql；否则返回分页语句Sql</param>
        /// <returns></returns>
        public static string OledbPage(int pageIndex, int pageSize, string whereStr, string tableName,string primaryKey, string showFields, string orderBy, int recordCount, bool isTotal)
        {
            //SELECT TOP 20 id,name,forShort FROM Area  
            //WHERE id IN(
            //         SELECT TOP 20 id FROM (SELECT TOP 600 id FROM Area WHERE 1=1 ORDER BY id DESC) AS S ORDER BY ID ASC
            //) ORDER BY ID DESC
            if (String.IsNullOrEmpty(whereStr))
            {
                whereStr = "1=1";
            }
            if (isTotal)
            {
                return String.Format("SELECT COUNT(*) AS Total FROM {0} WHERE {1} ", tableName, whereStr);
            }
            if ((showFields + "").Length == 0)
            {
                showFields = "*";
            }
            if (String.IsNullOrEmpty(orderBy))
            {
                throw new ArgumentNullException();
            }
            if (pageIndex == 0 || pageSize == 0)
            {
                return String.Format(OlepageAll, String.Empty, "*", tableName, whereStr);
            }
            else if (pageIndex == 1)//第一页
            {
                return String.Format(OlepageAll, "TOP " + pageSize, showFields, tableName, whereStr);
            }
            int max = (pageIndex - 1) * pageSize;
            if (max < 500)
            {
                return String.Format(OlepageTopNotIn, pageSize, tableName, max, whereStr, showFields, primaryKey, orderBy);
            }
            else
            {
                string order = "";
                orderBy = orderBy.ToUpper();
                if (orderBy.Contains("DESC"))
                {
                    order = orderBy.Replace("DESC", "ASC");
                }
                else
                {
                    order = orderBy.Replace("ASC", "DESC");
                }
                return String.Format(OlePageTop, recordCount - max > pageSize ? pageSize : recordCount - max, showFields, tableName, primaryKey, whereStr, orderBy, order, pageIndex * pageSize);
            }
        }
        /// <summary>
        /// SQLite分页查询 
        /// </summary>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="whereStr">查询条件</param>
        /// <param name="tableName">表</param>
        /// <param name="showFields">显示列</param>
        /// <param name="orderBy">排序：order by id desc</param>
        /// <param name="recordCount">行数</param>
        /// <param name="isTotal">如果isTotal为True，返回总数统计Sql；否则返回分页语句Sql</param>
        /// <returns></returns>
        public static string SQLitePage(int pageIndex, int pageSize, string whereStr, string tableName, string showFields, string orderBy, int recordCount, bool isTotal)
        {
            if (String.IsNullOrEmpty(showFields))
            {
                showFields = "*";
            }
            if (String.IsNullOrEmpty(whereStr))
            {
                whereStr = "1=1";
            }
            if (isTotal)
            {
                return String.Format("SELECT COUNT(*) AS Total FROM {0} WHERE {1} ", tableName, whereStr);
            }
            if ((orderBy + "").Length > 0)
            {
                orderBy = "ORDER BY " + orderBy;
            }
            else
            {
                orderBy = "";
            }
            if (pageIndex == 0 || pageSize == 0)
            {
                pageIndex = 1;
            }
            if (recordCount < pageSize)//只有一页
            {
                return String.Format(SQLitepageAll, showFields, tableName, whereStr, orderBy, pageSize, 0);
            }
            else
            {
                return String.Format(SQLitepageAll, showFields, tableName, whereStr, orderBy, pageSize, pageSize * (pageIndex - 1));
            }
        }
        /// <summary>
        /// 不依赖于存储过程的分页(MySql)
        /// </summary>
        /// <param name="isTotal">如果isTotal为True，返回总数统计Sql；否则返回分页语句Sql</param>
        /// <returns></returns>
        public static string MySqlPage(int pageIndex, int pageSize, string whereStr, string tableName, string showFields, string orderBy, int recordCount, bool isTotal)
        {
            string sql = "";
            if ((whereStr + "").Length == 0)
            {
                whereStr = " (1=1) ";
            }
            if ((orderBy + "").Length > 0)//SELECT * FROM 表名称 LIMIT M,N 
            {
                orderBy = String.Format(" ORDER BY {0} ", orderBy);
            }
            if (isTotal)
            {
                sql = String.Format("SELECT COUNT(*) AS Total FROM {0} WHERE {1} ", tableName, whereStr);
            }
            else
            {
                int minRow = pageSize * (pageIndex - 1);
                int maxRow = pageSize * pageIndex;
                sql = String.Format(MySqlpageAll, showFields, tableName, whereStr, orderBy, maxRow, minRow);
            }
            return sql;
        }

        #endregion

        #region 分页存储过程
        /// <summary>
        /// 获取sql2005分页存储过程[输出带"br换行标签"]
        /// </summary>
        public static string GetSelectBaseOutPutToHtmlForSql2005()
        {
            sqlText.Remove(0, sqlText.Length);
            sqlText.Append("create procedure sp_Paging <br />");
            sqlText.Append("@pageIndex		       int, <br />");
            sqlText.Append("@pageSize			   int, <br />");
            sqlText.Append("@TableName    nvarchar(4000), <br />");
            sqlText.Append("@Where 	   nvarchar(max)='' <br />");
            sqlText.Append("as <br />");
            sqlText.Append("Declare @rowcount  		int <br />");
            sqlText.Append("Declare @intStart  		int <br />");
            sqlText.Append("Declare @intEnd         int <br />");
            sqlText.Append("Declare @Sql nvarchar(max), @WhereR nvarchar(max), @OrderBy nvarchar(max) <br />");
            sqlText.Append("set @rowcount=0 <br />");
            sqlText.Append("set nocount on <br />");
            sqlText.Append("if @Where<>'' <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @Where=' AND '+@Where <br />");
            sqlText.Append("end <br />");
            sqlText.Append("if CHARINDEX('order by', @Where)>0 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1)	--取得条件 <br />");
            sqlText.Append("set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where))	--取得排序方式(order by 字段 方式) <br />");
            sqlText.Append("end <br />");
            sqlText.Append("else <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @WhereR=@Where <br />");
            sqlText.Append("set @OrderBy=' order by id asc' <br />");
            sqlText.Append("end <br />");
            sqlText.Append("set @Sql='SELECT @rowcount=count(*) FROM '+cast(@TableName as varchar(4000))+' WHERE 1=1 '+@WhereR <br />");

            sqlText.Append("exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output <br />");
            sqlText.Append("if @pageIndex=0 AND @pageSize=0	--不进行分页,查询所有数据列表 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @Sql='SELECT * FROM '+cast(@TableName as varchar(4000))+' WHERE 1=1 '+@Where <br />");
            sqlText.Append("end <br />");
            sqlText.Append("else	--进行分页查询数据列表 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @intStart=(@pageIndex-1)*@pageSize+1; <br />");
            sqlText.Append("set @intEnd=@intStart+@pageSize-1 <br />");
            sqlText.Append("set @Sql='SELECT * FROM(SELECT *,ROW_NUMBER() OVER('+cast(@OrderBy as nvarchar(400))+') as row FROM ' <br />");
            sqlText.Append("set @Sql=@Sql+@TableName+' WHERE 1=1 '+@WhereR+') as a WHERE row between '+cast(@intStart as varchar)+' AND '+cast(@intEnd as varchar) <br />");
            sqlText.Append("end <br />");
            sqlText.Append("exec sp_executeSql @Sql <br />");
            sqlText.Append("return @rowcount <br />");
            sqlText.Append("set nocount off <br />");
            return sqlText.ToString();
        }
        /// <summary>
        /// 获取sql2000分页存储过程[输出带"br换行标签"]
        /// </summary>
        public static string GetSelectBaseOutPutToHtmlForSql2000()
        {
            sqlText.Remove(0, sqlText.Length);
            sqlText.Append("create procedure sp_Paging <br />");
            sqlText.Append("@pageIndex		       int, <br />");
            sqlText.Append("@pageSize			   int, <br />");
            sqlText.Append("@TableName    nvarchar(4000), <br />");
            sqlText.Append("@Where 	   nvarchar(2000)='' <br />");
            sqlText.Append("as <br />");
            sqlText.Append("Declare @rowcount  		int <br />");
            sqlText.Append("Declare @intStart  		int <br />");
            sqlText.Append("Declare @intEnd         int <br />");
            sqlText.Append("declare @Column1 varchar(32) <br />");//第一列名称
            sqlText.Append("Declare @Sql nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000) <br />");
            sqlText.Append("set @rowcount=0 <br />");
            sqlText.Append("set nocount on <br />");
            sqlText.Append("if @Where<>'' <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @Where=' AND '+@Where <br />");
            sqlText.Append("end <br />");
            sqlText.Append("if CHARINDEX('order by', @Where)>0 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1)	--取得条件 <br />");
            sqlText.Append("set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where))	--取得排序方式(order by 字段 方式) <br />");
            sqlText.Append("end <br />");
            sqlText.Append("else <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @WhereR=@Where <br />");
            sqlText.Append("set @OrderBy=' order by id asc' <br />");
            sqlText.Append("end <br />");
            sqlText.Append("set @Sql='SELECT @rowcount=count(*) FROM '+cast(@TableName as varchar(4000))+' WHERE 1=1 '+@WhereR <br />");

            sqlText.Append("exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output <br />");
            sqlText.Append("if @pageIndex=0 AND @pageSize=0	--不进行分页,查询所有数据列表 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @Sql='SELECT * FROM '+cast(@TableName as varchar(4000))+' WHERE 1=1 '+@Where <br />");
            sqlText.Append("end <br />");
            sqlText.Append("else	--进行分页查询数据列表 <br />");
            sqlText.Append("begin <br />");
            sqlText.Append("set @intStart=(@pageIndex-1)*@pageSize+1; <br />");
            sqlText.Append("set @intEnd=@intStart+@pageSize-1 <br />");
            sqlText.Append("set @Column1=col_name(object_id(@tableName),1) <br />");//设置第一列名称
            sqlText.Append("set @Sql='Create table #tem(tempID int identity(1,1) NOT null,Row int) '  <br />");
            sqlText.Append("set @Sql=@Sql+'insert #tem(Row) SELECT '+@Column1+' FROM '+@TableName+' WHERE 1=1 '+@Where  <br />");
            sqlText.Append("set @Sql=@Sql+' SELECT t.* FROM '+@TableName+' t left join #tem  on t.'+@Column1+'=#tem.Row '  <br />");
            sqlText.Append("set @Sql=@Sql+' WHERE  #tem.tempID between '+cast(@intStart as varchar)+' AND '+cast(@intEnd as varchar) <br />");
            sqlText.Append("end <br />");
            sqlText.Append("exec sp_executeSql @Sql <br />");
            sqlText.Append("return @rowcount <br />");
            sqlText.Append("set nocount off <br />");
            return sqlText.ToString();
        }
        /// <summary>
        /// SQL Server分页存储过程
        /// </summary>
        /// <returns></returns>
        public static string GetOutPutToHtmlSQL()
        {
            sqlText.Remove(0, sqlText.Length);
            sqlText.Append("--分页存储过程  ");
            sqlText.Append("CREATE PROCEDURE [dbo].[sp_Paging] ");
            sqlText.Append("( ");
            sqlText.Append("@Tables nvarchar(1000),                --表名/视图名");
            sqlText.Append("@PrimaryKey nvarchar(100),             --主键");
            sqlText.Append("@Sort nvarchar(200) = NULL,            --排序字段(不带order by)");
            sqlText.Append("@pageindex int = 1,                    --当前页码");
            sqlText.Append("@pageSize int = 10,                    --每页记录数");
            sqlText.Append("@Fields nvarchar(1000) = N\'*\',         --输出字段");
            sqlText.Append("@Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)");
            sqlText.Append("@Group nvarchar(1000) = NULL,          --Group语句(不带Group By)");
            sqlText.Append("@TotalCount int OUTPUT                 --总记录数");
            sqlText.Append(") ");
            sqlText.Append("AS  ");
            sqlText.Append(" ");
            sqlText.Append("DECLARE @SortTable nvarchar(100) ");
            sqlText.Append("DECLARE @SortName nvarchar(100) ");
            sqlText.Append("DECLARE @strSortColumn nvarchar(200) ");
            sqlText.Append("DECLARE @operator char(2) ");
            sqlText.Append("DECLARE @type nvarchar(100) ");
            sqlText.Append("DECLARE @prec int ");
            sqlText.Append("");
            sqlText.Append("--设定排序语句");
            sqlText.Append("IF @Sort IS NULL OR @Sort = \'\'     ");
            sqlText.Append("   SET @Sort = @PrimaryKey      ");
            sqlText.Append("IF CHARINDEX(\'DESC\',@Sort)>0   ");
            sqlText.Append("BEGIN         ");
            sqlText.Append("    SET @strSortColumn = REPLACE(@Sort, \'DESC\', \'\')         ");
            sqlText.Append("    SET @operator = \'<=\'     ");
            sqlText.Append("END ");
            sqlText.Append("ELSE     ");
            sqlText.Append("BEGIN                ");
            sqlText.Append("    SET @strSortColumn = REPLACE(@Sort, \'ASC\', \'\')                ");
            sqlText.Append("    SET @operator = \'>=\'     ");
            sqlText.Append("END ");
            sqlText.Append("IF CHARINDEX(\'.\', @strSortColumn) > 0     ");
            sqlText.Append("BEGIN         ");
            sqlText.Append("    SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(\'.\',@strSortColumn))");
            sqlText.Append("    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(\'.\',@strSortColumn) + 1, LEN(@strSortColumn))     ");
            sqlText.Append("END ");
            sqlText.Append("ELSE     ");
            sqlText.Append("BEGIN         ");
            sqlText.Append("    SET @SortTable = @Tables         ");
            sqlText.Append("    SET @SortName = @strSortColumn  ");
            sqlText.Append("END ");
            sqlText.Append("");
            sqlText.Append("--设置排序字段类型和精度 ");
            sqlText.Append("SELECT @type=t.name, @prec=c.prec FROM sysobjects o ");
            sqlText.Append("       JOIN syscolumns c on o.id=c.id ");
            sqlText.Append("       JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName");
            sqlText.Append("        ");
            sqlText.Append("IF CHARINDEX(\'char\', @type) > 0    ");
            sqlText.Append("   SET @type = @type + \'(\' + CAST(@prec AS varchar) + \')\'");
            sqlText.Append("   ");
            sqlText.Append("DECLARE @strPageSize nvarchar(50) ");
            sqlText.Append("DECLARE @strStartRow nvarchar(50) ");
            sqlText.Append("DECLARE @strFilter nvarchar(1000) ");
            sqlText.Append("DECLARE @strSimpleFilter nvarchar(1000) ");
            sqlText.Append("DECLARE @strGroup nvarchar(1000)  ");
            sqlText.Append(" ");
            sqlText.Append("IF @pageindex <1     ");
            sqlText.Append("   SET @pageindex = 1  ");
            sqlText.Append("SET @strPageSize = CAST(@pageSize AS nvarchar(50)) ");
            sqlText.Append("--设置开始分页记录数 ");
            sqlText.Append("SET @strStartRow = CAST(((@pageindex - 1)*@pageSize + 1) AS nvarchar(50))  ");
            sqlText.Append("--筛选以及分组语句");
            sqlText.Append("IF @Filter IS NOT NULL AND @Filter != \'\'     ");
            sqlText.Append("BEGIN         ");
            sqlText.Append("    SET @strFilter = \' WHERE \' + @Filter + \' \' ");
            sqlText.Append("    SET @strSimpleFilter = \' AND \' + @Filter + \' \' ");
            sqlText.Append("END ");
            sqlText.Append("ELSE     ");
            sqlText.Append("BEGIN         ");
            sqlText.Append("    SET @strSimpleFilter = \'\'         ");
            sqlText.Append("    SET @strFilter = \'\'     ");
            sqlText.Append("END ");
            sqlText.Append("IF @Group IS NOT NULL AND @Group != \'\'  ");
            sqlText.Append("   SET @strGroup = \' GROUP BY \' ");
            sqlText.Append("--计算总记录数");
            sqlText.Append("DECLARE @TotalCountSql nvarchar(1000)");
            sqlText.Append("SET @TotalCountSql=N\'SELECT @TotalCount=COUNT(*)\' +N\' FROM \' + @Tables + @strFilter");
            sqlText.Append("EXEC sp_executesql @TotalCountSql,N\'@TotalCount int OUTPUT\',@TotalCount OUTPUT");
            sqlText.Append("--执行查询语句    ");
            sqlText.Append("EXEC(");
            sqlText.Append("\'");
            sqlText.Append("DECLARE @SortColumn \' + @type + \'");
            sqlText.Append("SET ROWCOUNT \' + @strStartRow + \'");
            sqlText.Append("SELECT @SortColumn=\' + @strSortColumn + \' FROM \' + @Tables + @strFilter + \' \' + @strGroup + \' ORDER BY \' + @Sort + \'");
            sqlText.Append("SET ROWCOUNT \' + @strPageSize + \'");
            sqlText.Append("SELECT \' + @Fields + \' FROM \' + @Tables + \' WHERE \' + @strSortColumn + @operator + \' @SortColumn \' + @strSimpleFilter + \' \' + @strGroup + \' ORDER BY \' + @Sort + \'");
            sqlText.Append("\'");
            sqlText.Append(")");
            sqlText.Append("GO");

            return sqlText.ToString();
        }

        public static string GetSelectBaseOutPutToHtmlForOracle()
        {
            return GetPackageHeadForOracle() + GetPackageBodyForOracle();
        }
        public static string GetPackageHeadForOracle()
        {
            return @"
           create or replace package MyPackage as 
type MyCursor is ref cursor;
procedure sp_Paging(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor);
end MyPackage;";
        }
        public static string GetPackageBodyForOracle()
        {
            return @"
create or replace package Body MyPackage is 
 procedure sp_Paging(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor)
  is
  rowStart  int;
  rowEnd    int;
  mySql varchar2(8000);
  whereOnly varchar2(8000);
  OrderOnly varchar2(400);
  begin
    mySql:='SELECT count(*) FROM '||tableName;
    whereOnly:=whereStr;
    rowStart:=instr(whereStr,'order by');
    if whereStr is NOT null AND  rowStart>0 
      then
        whereOnly:=substr(whereStr, 1,rowStart-1);
        OrderOnly:=substr(whereStr,rowStart, length(whereStr)-rowStart+1);
        end if;  
    if length(whereOnly)>1
      then
       whereOnly:=' WHERE '|| whereOnly;
       mySql:=mySql||whereOnly;
        end if;
        execute immediate mySql into resultCount;

        if pageIndex=0 AND pageSize=0	
        then 
        mySql:='SELECT * FROM '||tableName||whereOnly||OrderOnly;
       else

        rowStart:=(pageIndex-1)*pageSize+1; 
        rowEnd:=rowStart+pageSize-1;
        mySql:='SELECT * FROM (SELECT t.*,RowNum as rn FROM ('||tableName||') t'||whereOnly||OrderOnly||') WHERE rn between '||rowStart||' AND '||rowEnd; 
        end if;
    open ResultCursor for mySql;
    end sp_Paging;
  end MyPackage;";
        }
        #endregion

        #region 通用分页SQL语句

        #region ConstructSplitSQL
        /// <summary>
        /// 获取分页操作SQL语句(对于排序的字段必须建立索引，优化分页提取方式)
        /// </summary>
        /// <param name="tableName">操作表名称</param>
        /// <param name="fldName">排序的索引字段</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">总记录数</param>
        /// <param name="orderType">排序方式(0升序，1为降序)</param>
        /// <param name="whereStr">检索的条件语句，不需要再加WHERE关键字</param>
        /// <returns></returns>
        public static string ConstructSplitSQL(string tableName, string fldName, int pageIndex, int pageSize, int totalRecord, int orderType, string whereStr)
        {
            string strSQL = "";
            string strOldWhere = "";
            string rtnFields = "*";

            // 构造检索条件语句字符串
            if (whereStr != "")
            {
                // 去除不合法的字符，防止SQL注入式攻击
                whereStr = whereStr.Replace("'", "''");
                whereStr = whereStr.Replace("--", "");
                whereStr = whereStr.Replace(";", "");

                strOldWhere = " AND " + whereStr + " ";
                whereStr = " WHERE " + whereStr + " ";
            }

            // 升序操作
            if (orderType == 0)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " ASC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " ASC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
                }
            }
            // 降序操作
            else if (orderType == 1)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " DESC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " DESC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
                }
            }
            else // 异常处理
            {
                throw new DataException("未指定任何排序类型。0升序，1为降序");
            }
            return strSQL;
        }

        /// <summary>
        /// 获取分页操作SQL语句(对于排序的字段必须建立索引)
        /// </summary>
        /// <param name="tableName">操作表名</param>
        /// <param name="fldName">操作索引字段名称</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
        /// <param name="orderType">排序方式(0升序，1为降序)</param>
        /// <param name="whereStr">检索的条件语句，不需要再加WHERE关键字</param>
        /// <returns></returns>
        public static string ConstructSplitSQL(string tableName, string fldName, int pageIndex, int pageSize, string rtnFields, int orderType, string whereStr)
        {
            string strSQL = "";
            string strOldWhere = "";

            // 构造检索条件语句字符串
            if (whereStr != "")
            {
                // 去除不合法的字符，防止SQL注入式攻击
                whereStr = whereStr.Replace("'", "''");
                whereStr = whereStr.Replace("--", "");
                whereStr = whereStr.Replace(";", "");

                strOldWhere = " AND " + whereStr + " ";
                whereStr = " WHERE " + whereStr + " ";
            }

            // 升序操作
            if (orderType == 0)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " ASC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " ASC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
                }
            }
            // 降序操作
            else if (orderType == 1)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " DESC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " DESC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
                }
            }
            else // 异常处理
            {
                throw new DataException("未指定任何排序类型。0升序，1为降序");
            }
            return strSQL;
        }

        /// <summary>
        /// 获取分页操作SQL语句(对于排序的字段必须建立索引)
        /// </summary>
        /// <param name="tableName">操作表名</param>
        /// <param name="fldName">操作索引字段名称</param>
        /// <param name="unionCondition">用于连接的条件，例如: LEFT JOIN UserInfo u ON (u.UserID = b.UserID)</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
        /// <param name="orderType">排序方式，0升序，1为降序</param>
        /// <param name="whereStr">检索的条件语句，不需要再加WHERE关键字</param>
        /// <returns></returns>
        public static string ConstructSplitSQL(string tableName, string fldName, string unionCondition, int pageIndex, int pageSize, string rtnFields, int orderType, string whereStr)
        {
            string strSQL = "";
            string strOldWhere = "";

            // 构造检索条件语句字符串
            if (whereStr != "")
            {
                whereStr = whereStr.Replace("'", "''");
                whereStr = whereStr.Replace("--", "");
                whereStr = whereStr.Replace(";", "");

                strOldWhere = " AND " + whereStr + " ";
                whereStr = " WHERE " + whereStr + " ";
            }

            // 升序操作
            if (orderType == 0)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + unionCondition + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " ASC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + unionCondition + " ";
                    strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " ASC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
                }
            }
            // 降序操作
            else if (orderType == 1)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + unionCondition + " ";
                    strSQL += whereStr + "ORDER BY " + fldName + " DESC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + unionCondition + " ";
                    strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " DESC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
                }
            }
            else // 异常处理
            {
                throw new DataException("未指定任何排序类型。0升序，1为降序");
            }

            return strSQL;
        }
        #endregion

        #region ConstructSplitSQL_TOP
        /// <summary>
        /// 获取分页操作SQL语句(对于排序的字段必须建立索引)
        /// </summary>
        /// <param name="tableName">操作表名</param>
        /// <param name="fldName">操作索引字段名称</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
        /// <param name="orderType">排序方式(0升序，1为降序)</param>
        /// <param name="whereStr">检索的条件语句，不需要再加WHERE关键字</param>
        /// <returns></returns>
        public static string ConstructSplitSQL_TOP(string tableName, string fldName, int pageIndex, int pageSize, string rtnFields, int orderType, string whereStr)
        {
            string strSQL = "";
            string strOldWhere = "";

            // 构造检索条件语句字符串
            if (whereStr != "")
            {
                whereStr = whereStr.Replace("'", "''");
                whereStr = whereStr.Replace("--", "");
                whereStr = whereStr.Replace(";", "");

                strOldWhere = " AND " + whereStr + " ";
                whereStr = " WHERE " + whereStr + " ";
            }

            // 升序操作
            if (orderType == 0)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + " ORDER BY " + fldName + " ASC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " ASC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
                }
            }
            // 降序操作
            else if (orderType == 1)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + " ORDER BY " + fldName + " DESC";
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + " ORDER BY " + fldName + " DESC ) AS T )) ";
                    strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
                }
            }
            else // 异常处理
            {
                throw new DataException("未指定任何排序类型。0升序，1为降序");
            }

            return strSQL;
        }

        #endregion

        #region ConstructSplitSQL_Sort(指定排序的表达式)

        /// <summary>
        /// 获取分页操作SQL语句(对于排序的字段必须建立索引)
        /// </summary>
        /// <param name="tableName">操作表名</param>
        /// <param name="fldName">操作索引字段名称</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
        /// <param name="orderType">排序方式(0升序，1为降序)</param>
        /// <param name="sort">排序表达式</param>
        /// <param name="whereStr">检索的条件语句，不需要再加WHERE关键字</param>
        /// <returns></returns>
        public static string ConstructSplitSQL_Sort(string tableName, string fldName, int pageIndex, int pageSize, string rtnFields, int orderType, string sort, string whereStr)
        {
            string strSQL = "";
            string strOldWhere = "";

            if (whereStr != "")
            {
                // 去除不合法的字符，防止SQL注入式攻击
                whereStr = whereStr.Replace("'", "''");
                whereStr = whereStr.Replace("--", "");
                whereStr = whereStr.Replace(";", "");

                strOldWhere = " AND " + whereStr + " ";
                whereStr = " WHERE " + whereStr + " ";
            }

            if (sort != "") sort = " ORDER BY " + sort;

            // 升序操作
            if (orderType == 0)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + sort;
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + sort + " ) AS T )) ";

                    strSQL += strOldWhere + sort;
                }
            }
            // 降序操作
            else if (orderType == 1)
            {
                if (pageIndex == 1)
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";
                    strSQL += whereStr + sort;
                }
                else
                {
                    strSQL += "SELECT TOP " + pageSize + " " + rtnFields + " FROM " + tableName + " ";

                    strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " " + fldName + " FROM " + tableName + whereStr + sort + " ) AS T )) ";

                    strSQL += strOldWhere + sort;
                }
            }
            else // 异常处理
            {
                throw new DataException("未指定主索引排序类型。0升序，1为降序");
            }

            return strSQL;
        }

        #endregion

        #endregion
    }
}